﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class TeacherDataProvider
    {
        ConnectDB con = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;

        

        //Them nhan vien vao bang va tra ve ID cua giao vien
        public int AddTeacher(string ID, string name, string genderid, string birthday, string phone, string email, string addr)
        {
            

            string query = string.Format("insert into Teachers (ID, FullName, GenderID, Birthday, PhoneNum, Email, Address,NewRecord, CreateDateTime) values " +
               "('{0}','{1}',{2},'{3}',{4},'{5}','{6}',{7} ,'{8}')", ID, name, genderid, birthday, phone, email, addr,1 , DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public int deleteTeacher(string id)
        {
            string query = string.Format("Delete from Teachers where ID = '{0}'", id);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        //Chon het giao vien
        public DataSet SelectAllTeacher()
        {
            string query = string.Format("Select * from Teachers");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Lay giao vien co Id va chuyen sang xml
        public string SelectAndConvertXMLTech(string idTeach)
        {
            string query = string.Format("select * from Teachers where ID = '{0}' for xml auto,elements", idTeach);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Lay id cua cot co record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select ID from Teachers where NewRecord = 1");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update Teachers set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay id cua cot co record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select ID from Teachers where NewRecord = 2");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update Teachers set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 

        //Load danh sach giao vien day mon hoc nao do
        public DataSet LoadTeacher(string idSub)
        {
            string query = string.Format("Select ID,FullName from Teachers,TeacherTeach "+
                "where Teachers.ID = TeacherTeach.IDTeacher and TeacherTeach.IDSubject = '{0}'", idSub);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
    }
}
